﻿/*
 * 文件名：logined.cs
 * 类名：XHCTMS.DAL.logined
 * 创建人：仇祖萍
 * 创建时间：2013-05-20
 * 备注：
 * 
 */
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace XHCTMS.DAL
{
    #region 存储过程
 //查询                                    
//CREATE PROC SEARCH_login_INFO

//@UserId int,           
//@Logintime  VARCHAR(32)=NULL
//AS
//BEGIN
//SET NOCOUNT ON
//SELECT logined.login_Id, [user].[user_name], logined.login_time FROM     
//logined , [user]
           
//WHERE (@UserId IS NULL OR [user_id]=@UserId)
//  AND	(@Logintime IS NULL OR [login_time]=@Logintime) 
//END

//EXEC SEARCH_login_INFO 2013001,NULL

//insert into logined values('2013001',GETDATE())
//insert into logined values('2013002',GETDATE())
//insert into logined values('2013003',GETDATE())
//insert into logined values('2013004',GETDATE())
//insert into logined values('2013005',GETDATE())
//insert into logined values('2013006',GETDATE())
//insert into logined values('2013007',GETDATE())
//insert into logined values('2013008',GETDATE())

    //分页
    //    create view v_login  

    //As  

    //Select login_Id,[user_name],login_time

    //From logined,[user]  

    //Where login_user_Id=[user_id]


       //CREATE PROC proc_login
       //     @PAGESIZE INT,					--页面的大小
       //     @PAGEINDEX INT					--当然的页号
       //     AS
       //     BEGIN
       //      WITH TEMP AS (
       //     SELECT ROW_NUMBER() OVER (ORDER BY login_Id ASC)AS ROW, * FROM v_login )

       //      SELECT * FROM TEMP WHERE ROW BETWEEN 

       //     (@PAGEINDEX-1)*@PAGESIZE+1 AND (@PAGEINDEX-1)*@PAGESIZE+@PAGESIZE

       //     END

       //     EXEC proc_login 2,3

    #endregion 

    /// <summary>
    /// 对登录用户进行查询操作
    /// </summary>
    public class logined
    {
        /// <summary>
        /// 分页获取登录用户信息
        /// </summary>
        /// <param name="pageSize">页面大小</param>
        /// <param name="pageIndex">当前页号</param>
        /// <returns>返回DataTable类型  null/异常</returns>
        public DataTable Paging(int pageSize, int pageIndex)
        {
            StringBuilder queryString = new StringBuilder();

            queryString.Append("proc_login");

            SqlParameter[] paras = {
                                       new SqlParameter("@PageSize", SqlDbType.Int),
                                       new SqlParameter("@PageIndex", SqlDbType.Int)
                                   };

            paras[0].Value = pageSize;
            paras[1].Value = pageIndex;

            return new DBHELP.Common().GetDataTableProc(queryString.ToString(), paras);
        }

        /// <summary>
        /// 查询所有用户登录信息
        /// </summary>
        /// <returns>返回DataTable类型 null/异常</returns>
        public DataTable View()
        {
            StringBuilder queryString = new StringBuilder().Append(@" select logined.login_id, [user].[user_name],logined.login_time 
            from 
            logined,[user]
            where 
            logined.login_user_id=[user].[user_id]");

            return new DBHELP.Common().GetDataTable(queryString.ToString());
        }

        /// <summary>
        /// 按条件查询用户登录信息,分页显示结果
        /// </summary>
        /// <param name="pageIndex">页号</param>
        /// <param name="query"></param>
        /// <returns>返回DataTable类型 null/异常</returns>
        public DataTable SelectByCondition(int pageIndex, string query)
        {
            StringBuilder queryString = new StringBuilder();

            queryString.Append(@"  select top 50
            logined.login_id, [user].[user_name],logined.login_time 
            from 
            logined,[user]
            where 
            logined.login_user_id=[user].[user_id]
            not in
            (select top (50*@pageindex) logined.login_id 
            from 
            logined,[user]
            where 
            logined.login_user_id=[user].[user_id]
            and
            ([user].[user_name] like '%'+@Query+'%'or logined.login_time like '%'+@Query+'%')");

            SqlParameter[] Para ={               
                                     new SqlParameter("@PageIndex", SqlDbType.Int),
                                     new SqlParameter("@Query",SqlDbType.VarChar,32)
                                };

            Para[0].Value = pageIndex;
            Para[1].Value = query;

            return new DBHELP.Common().GetDataTable(queryString.ToString(), Para);
        }

        /// <summary>
        /// 多条件查询
        /// </summary>
        /// <param name="login">用户登录信息</param>
        /// <param name="user">用户信息</param>
        /// <returns></returns>
        public DataTable ViewBy(MODEL.logined login)
        {
            StringBuilder queryString = new StringBuilder();

            queryString.Append(@"SEARCH_login_INFO");
            SqlParameter[] Para = {
                                      new SqlParameter("@UserId",SqlDbType.Int),
                                      new SqlParameter("@Logintime", SqlDbType.VarChar, 32)
                                   };
            if (login.LoginUserId != null)
            {
                Para[0].Value = login.LoginUserId;
            }
            else
            {
                Para[0].Value = null;
            }
            if (login.LoginTime != null)
            {
                Para[1].Value = login.LoginTime;
            }
            else
            {
                Para[1].Value = null;
            }
            return new DBHELP.Common().GetDataTableProc(queryString.ToString(), Para);
        }

        /// <summary>
        /// 得到用户名
        /// </summary>
        /// <returns></returns>
        public DataTable GetUserName()
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append("select [user].[user_id], [user].[user_name] from logined,[user] where login_user_Id=[user_id] ");
            return new DBHELP.Common().GetDataTable(queryString.ToString());
        }

        /// <summary>
        /// 查询结果集的数目
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns>返回DataTable类型 null/异常</returns>
        public string GetAmount(string[] parasValue)
        {
            string[] parasStr = { " tu.[user_id] =", " tl.login_time=" };
            string[] parasName = { "@loginUserName", "@loginTime" };

            List<SqlParameter> listParas = new List<SqlParameter>();
            StringBuilder queryString = new StringBuilder(" select ((COUNT(login_Id )-1)/5+1) from logined tl left join [user] tu on tl.login_user_Id=tu.[user_id]  ");
            bool isFirst = true;
            for (int count = 0; count < parasValue.Length; count++)
            {
                if (!string.IsNullOrEmpty(parasValue[count]))
                {
                    if (isFirst)
                    {
                        queryString.Append(" where ");
                        isFirst = false;
                    }
                    else
                    {
                        queryString.Append(" and ");
                    }
                    if (count == 0)
                    {
                        queryString.Append(parasStr[count] + parasName[count]);
                        listParas.Add(new SqlParameter(parasName[count], parasValue[count]));
                    }
                    if (count == 1)
                    {
                        queryString.Append(" convert(datetime,tl.login_time,100)>="+parasName[count]+" and convert(datetime,tl.login_time,100)<=@nextDay" );
                        try
                        {
                            listParas.Add(new SqlParameter(parasName[count], parasValue[count]));
                            listParas.Add(new SqlParameter("@nextDay", DateTime.Parse(parasValue[count]).AddDays(1).ToShortDateString()));
                        }
                        catch
                        {
                            return "DateError";
                        }
                    }
                }

            }


            return new DBHELP.Common().GetDataScalar(queryString.ToString(),listParas);

        }

        public DataTable Search(string[] parasValue, int page =1)
        {
            DataTable dt = new DataTable();
            string[] parasStr = { " login_user_id =", " login_time=" };
            string[] parasName = { "@loginUserName", "@loginTime" };

            if (parasValue.Length > parasStr.Length)
            {
                dt.TableName = "ArraryLengthError";
                return dt;
            }
            StringBuilder sb = new StringBuilder(@"select top 5 tl.login_Id,tu.[user_name],tl.login_user_Id,tl.login_time from logined tl left join [user] tu 
              on tl.login_user_Id = tu.[user_id]   ");
            StringBuilder sb2 = new StringBuilder(@"  tl.login_Id not in (select top(5*@page) tl.login_Id from logined tl left join [user] tu 
              on tl.login_user_Id = tu.[user_id]");
            List<SqlParameter> listParas = new List<SqlParameter>();
            bool isFirst = true;
            for (int count = 0; count < parasValue.Length; count++)
            {
                if (!string.IsNullOrEmpty(parasValue[count]))
                {
                    if (isFirst)
                    {
                        sb.Append(" where ");
                        sb2.Append(" where ");
                        isFirst = false;
                    }
                    else
                    {
                        sb.Append(" and ");
                        sb2.Append(" and ");
                    }
                    if (count == 0)
                    {
                        sb.Append(parasStr[count] + parasName[count]);
                        sb2.Append(parasStr[count] + parasName[count]);
                        listParas.Add(new SqlParameter(parasName[count], parasValue[count]));
                    }
                    if (count == 1)
                    {
                        sb.Append(" convert(datetime,tl.login_time,100)>="+parasName[count]+" and convert(datetime,tl.login_time,100)<=@nextDay" );
                        sb2.Append(" convert(datetime,tl.login_time,100)>=" + parasName[count] + " and convert(datetime,tl.login_time,100)<=@nextDay");
                        listParas.Add(new SqlParameter(parasName[count], parasValue[count]));
                        try
                        {
                            listParas.Add(new SqlParameter("@nextDay", DateTime.Parse(parasValue[count]).AddDays(1).ToShortDateString()));
                        }
                        catch
                        {
                            return  dt = null;
                        }
                    }
                }
            }
            if (isFirst)
            {
                sb.Append(" where ");
            }
            else
            {
                sb.Append(" and ");
            }
            sb.Append(sb2.ToString() + " )");
            listParas.Add(new SqlParameter("@page", page -1));
            return new DBHELP.Common().GetDataTable(sb.ToString(), listParas);

        }

    }
}
